package com.pic.mgt.utils;

import java.util.Vector;

public abstract class SqlScriptUtils {
	public static enum FieldType {Numeric, NotNumeric};
	
	/**
	 * 建立一個 Delete 語法 PreparedStatement Script，條件值的部份是採用 ':欄位名稱' 做代替。
	 * @param tableName
	 * @param conditionField 不能為 Null。
	 * @return
	 */
	public static String createNamedParamDeleteScript(
			String tableName, String conditionField) {
		if (null == tableName || null == conditionField) {
			return null;
		}
		
		String sqlConditionStr = concatNamedParamWhereCondition(conditionField);
		
		return createFinalDeleteScript(tableName, sqlConditionStr);
	}
	
	/**
	 * 建立一個 Delete 語法 PreparedStatement Script，條件值的部份是採用 ':欄位名稱' 做代替。
	 * @param tableName
	 * @param conditionField1 和 conditionField2 不能為 Null。
	 * @return
	 */
	public static String createNamedParamDeleteScript(
			String tableName, String conditionField1, String conditionField2) {
		if (null == tableName || null == conditionField1 || null == conditionField2) {
			return null;
		}
		
		String sqlConditionStr1 = concatNamedParamWhereCondition(conditionField1);
		String sqlConditionStr2 = concatNamedParamWhereCondition(conditionField2);
		
		return createFinalDeleteScript(tableName, sqlConditionStr1, sqlConditionStr2);
	}
	
	public static String createFinalDeleteScript(String tableName, String sqlConditionStr) {
		StringBuffer sqlScriptBuf = new StringBuffer();

		sqlScriptBuf.append("DELETE FROM ");
		sqlScriptBuf.append(tableName + " ");
		sqlScriptBuf.append("WHERE" + " ");
		sqlScriptBuf.append(sqlConditionStr);
		
		return sqlScriptBuf.toString();
	}
	
	private static String createFinalDeleteScript(String tableName, String sqlConditionStr1, String sqlConditionStr2) {
		StringBuffer sqlScriptBuf = new StringBuffer();

		sqlScriptBuf.append("DELETE FROM ");
		sqlScriptBuf.append(tableName + " ");
		sqlScriptBuf.append("WHERE" + " ");
		sqlScriptBuf.append(sqlConditionStr1);
		sqlScriptBuf.append(" AND ");
		sqlScriptBuf.append(sqlConditionStr2);
		
		return sqlScriptBuf.toString();
	}
	
	/**
	 * 建立一個 Query 語法 PreparedStatement Script，條件值的部份是採用 ':欄位名稱' 做代替。
	 * @param tableName
	 * @param fieldsVector 不能為 null，若要 select 全部欄位，建議將欄位都放入 Vector 中當做參數傳入。
	 * @param conditionField 可以為 null，表示要 select 全部的資料。
	 * @param sorting 排序.格式為 String {'排序欄位名稱','DESC or ASC'}
	 * @return
	 */
	public static String createNamedParamQueryScriptWithSorting(String tableName, Vector<String> fieldsVector,
													  String conditionField, String[] sorting) {
		if (null == tableName || null == sorting ||
				null == fieldsVector || fieldsVector.isEmpty()) {
				return null;
			}
		
		String sqlFieldsStr = concatQueryFields(fieldsVector);
		String sqlConditionStr = concatNamedParamWhereCondition(conditionField);
		String sqlOrderByStr = concatQueryOrderBy(sorting);
		
		return
			createFinalQueryScript(tableName, sqlFieldsStr, sqlConditionStr, sqlOrderByStr);
	}
	
	/**
	 * 建立一個 Query 語法 PreparedStatement Script，條件值的部份是採用 ':欄位名稱' 做代替。
	 * @param tableName
	 * @param fieldsVector 不能為 null，若要 select 全部欄位，建議將欄位都放入 Vector 中當做參數傳入。
	 * @param conditionFieldAndOperator 必須代入陣列，表示為多個條件。 ex: [0]: field name [1]: "AND" [2]: field name ....
	 * 					            	 所以陣列長度一定是積數
	 * @return
	 */
	public static String createNamedParamQueryScriptWithMultiConditions(String tableName,
												     Vector<String> fieldsVector,
													 String[] conditionFieldAndOperator) {
		if (null == tableName || 
				null == fieldsVector || fieldsVector.isEmpty()) {
				return null;
		}
		
		String sqlFieldsStr = concatQueryFields(fieldsVector);
		String sqlConditionStr = concatNamedParamWhereCondition(conditionFieldAndOperator);
		
		return
			createFinalQueryScript(tableName, sqlFieldsStr, sqlConditionStr);
	}
	
	/**
	 * 建立一個 Query 語法 PreparedStatement Script，條件值的部份是採用 ':欄位名稱' 做代替。
	 * @param tableName
	 * @param fieldsVector 不能為 null，若要 select 全部欄位，建議將欄位都放入 Vector 中當做參數傳入。
	 * @param conditionField 可以為 null，表示要 select 全部的資料。
	 * @return
	 */
	public static String createNamedParamQueryScriptWithSingleCondition(
										   String tableName,
										   Vector<String> fieldsVector,
										   String conditionField) {
		if (null == tableName || 
			null == fieldsVector || fieldsVector.isEmpty()) {
			return null;
		}
		
		String sqlFieldsStr = concatQueryFields(fieldsVector);
		String sqlConditionStr = concatNamedParamWhereCondition(conditionField);
		
		return
			createFinalQueryScript(tableName, sqlFieldsStr, sqlConditionStr);
	}
	
	/**
	 * 建立一個 Query 語法 PreparedStatement Script，條件值的部份是採用 ":欄位名稱"做代替，並且再帶一個可直接給定的條件式。
	 * @param tableName tableName 不能為 null。可使用多個 table , ex: "table1,table2"
	 * @param fieldsVector
	 * @param givenCondition 必須要給值,不能為null。EX: aaa='abc' 或  field1 = field2
	 * @param conditionField 不能為 Null。
	 * @return
	 */
	// (送多方新增)
	public static String createNamedParamQueryScriptWithSingleAndGivenCondition(
										   String tableName,
										   Vector<String> fieldsVector,
										   String givenCondition,
										   String conditionField) {
		if (null == tableName || 
			null == fieldsVector || fieldsVector.isEmpty() ||
			givenCondition == null || givenCondition.isEmpty() ||
			conditionField == null || conditionField.isEmpty()) {
				return null;
		}
		
		String sqlFieldsStr = concatQueryFields(fieldsVector);
		String sqlConditionStr = concatNamedParamWhereCondition(conditionField);
		
		sqlConditionStr = sqlConditionStr + " AND " + givenCondition;
		
		return
			createFinalQueryScript(tableName, sqlFieldsStr, sqlConditionStr);
	}
	
	/**
	 * 建立一個 Query 語法 PreparedStatement Script，條件值的部份是採直接給定的方式。
	 * @param tableName 不能為 null。可使用多個 table , ex: "table1,table2"
	 * @param fieldsVector 不能為 null，若要 select 全部欄位，建議將欄位都放入 Vector 中當做參數傳入。
	 * @param givenConditionField 必須要給值,不能為null。EX: aaa='abc' 或  field1 = field2
	 * @return
	 */
	// (送多方新增)
	public static String createNamedParamQueryScriptWithGivenCondition(String tableName,
										Vector<String> fieldsVector,
										String givenConditionField) {
		if (null == tableName || 
			null == fieldsVector || fieldsVector.isEmpty() ||
			null == givenConditionField || givenConditionField.isEmpty()) {
				return null;
			}
			
			String sqlFieldsStr = concatQueryFields(fieldsVector);

			return
				createFinalQueryScript(tableName, sqlFieldsStr, givenConditionField);
	}
	
	
	private static String concatQueryFields(Vector<String> queryFieldsVector) {
		StringBuffer queryFieldsStringBuf = new StringBuffer();

		if (!queryFieldsVector.isEmpty()) {
			for(String fieldName : queryFieldsVector) {
				queryFieldsStringBuf.append(" ," + fieldName);
			}

			queryFieldsStringBuf.delete(0, 2);
		}

		return queryFieldsStringBuf.toString();
	}
	
	private static String concatQueryOrderBy(String[] sorting) {
		String sortField = sorting[0];
		String sortWay = sorting[1];
		
		return "ORDER BY " + sortField + " " + sortWay;	
	}
	
	private static String concatNamedParamWhereCondition(String conditionField) {
		StringBuffer conditionStringBuf = new StringBuffer();
		
		if (null != conditionField) {
			conditionStringBuf.append(conditionField);
			conditionStringBuf.append("=");
			conditionStringBuf.append(":" + conditionField);
			
			return conditionStringBuf.toString();
		}
		else {
			return null;
		}
	}
	
	private static String concatNamedParamWhereCondition(String[] codititonFieldAndOperator) {
		StringBuffer conditionStringBuf = new StringBuffer();
		
		if (null != codititonFieldAndOperator) {
			boolean isEvenNumber = true;
			
			for (int i=0;i<codititonFieldAndOperator.length;i++) {
				if (isEvenNumber) {
					String conditionField = codititonFieldAndOperator[i];
					
					conditionStringBuf.append(conditionField);
					conditionStringBuf.append("=");
					conditionStringBuf.append(":" + conditionField);
				}
				else {
					String operator = codititonFieldAndOperator[i];
					
					conditionStringBuf.append(" ");
					conditionStringBuf.append(operator);
					conditionStringBuf.append(" ");
				}
				isEvenNumber = !isEvenNumber;
			}
			return conditionStringBuf.toString();
		}
		else {
			return null;
		}
	}
	
	private static String createFinalQueryScript(String tableName,
			String sqlFieldsStr,
			String sqlConditionStr,
			String sqlOrderByStr) {
		return
			createFinalQueryScript(tableName, sqlFieldsStr, sqlConditionStr) + " " +
			sqlOrderByStr;
	}
	
	private static String createFinalQueryScript(String tableName,
												 String sqlFieldsStr,
												 String sqlConditionStr) {
		StringBuffer sqlScriptBuf = new StringBuffer();

		sqlScriptBuf.append("SELECT ");
		sqlScriptBuf.append(sqlFieldsStr + " ");
		sqlScriptBuf.append("FROM ");
		sqlScriptBuf.append(tableName + " ");
		
		if (null != sqlConditionStr) {
			sqlScriptBuf.append("WHERE ");
			sqlScriptBuf.append(sqlConditionStr);
		}

		return sqlScriptBuf.toString();
	}
	
	/**
	 * 建立一個 Insert 語法 PreparedStatement Script，欄位值的部份是採用 ? 做代替(欄位值的?順序即為所提供的向量參數順序)。
	 * @param tableName
	 * @param fieldsVector
	 * @return 
	 */
	public static String createInsertScript(String tableName,
											Vector<String> fieldsVector) {
		if (null == tableName || null == fieldsVector) {
			return null;
		}
		
		String sqlFieldsStr = concatInsertFields(fieldsVector);
		String sqlValuesStr = concatInsertValues(fieldsVector);
		
		String sqlScript = createFinalInsertScript(tableName, sqlFieldsStr, sqlValuesStr);
		
		return sqlScript;
	}
	
	private static String concatInsertValues(Vector<String> fieldsVector) {
		StringBuffer fieldsStringBuf = new StringBuffer();
		
		if (!fieldsVector.isEmpty()) {
			for(int i=0;i<fieldsVector.size();i++) {
				fieldsStringBuf.append(", ?");
			}
			
			fieldsStringBuf.delete(0, 2);
		}
		
		return "(" +  fieldsStringBuf.toString() + ")";
	}
	
	/**
	 * 建立一個 Insert 語法 PreparedStatement Script，欄位值的部份是採用 ':欄位名稱' 做代替。
	 * @param tableName
	 * @param fieldsVector
	 * @return 
	 */
	public static String createNamedParamInsertScript(String tableName,
											Vector<String> fieldsVector) {
		if (null == tableName || null == fieldsVector) {
			return null;
		}
		
		String sqlFieldsStr = concatInsertFields(fieldsVector);
		String sqlValuesStr = concatNamedInsertValues(fieldsVector);
		
		return createFinalInsertScript(tableName, sqlFieldsStr, sqlValuesStr);
	}
	
	private static String createFinalInsertScript(String tableName,
												  String sqlFieldsStr,
												  String sqlValuesStr) {
		StringBuffer sqlScriptBuf = new StringBuffer();
		
		sqlScriptBuf.append("INSERT INTO ");
		sqlScriptBuf.append(tableName + " ");
		sqlScriptBuf.append(sqlFieldsStr + " ");
		sqlScriptBuf.append("VALUES ");
		sqlScriptBuf.append(sqlValuesStr);
		
		return sqlScriptBuf.toString();
	}
	
	private static String concatInsertFields(Vector<String> fieldsVector) {
		StringBuffer fieldsStringBuf = new StringBuffer();
		
		if (!fieldsVector.isEmpty()) {
			for(String fieldName : fieldsVector) {
				fieldsStringBuf.append(" ," + fieldName);
			}
			
			fieldsStringBuf.delete(0, 2);
		}
		
		return "(" +  fieldsStringBuf.toString() + ")";
	}
	
	private static String concatNamedInsertValues(Vector<String> fieldsVector) {
		StringBuffer fieldsStringBuf = new StringBuffer();
		
		if (!fieldsVector.isEmpty()) {
			for(String fieldName : fieldsVector) {
				fieldsStringBuf.append(" ,:" + fieldName);
			}
			
			fieldsStringBuf.delete(0, 2);
		}
		
		return "(" +  fieldsStringBuf.toString() + ")";
	}
	
	/**
	 * 建立一個 Update 語法 PreparedStatement Script，欄位值的部份是採用 ? 做代替(欄位值的?順序即為所提供的向量參數順序)。
	 * @param tableName
	 * @param fieldsVector
	 * @return 
	 */
	public static String createUpdateScript (String tableName,
			Vector<String> fieldsVector, String conditionField) {
		if (null == tableName || null == fieldsVector || null == conditionField) {
			return null;
		}
		
		StringBuffer sqlScriptBuf = new StringBuffer();

		String sqlUpdateValuesStr = concatUpdateValues(fieldsVector);
		String sqlUpdateConditionStr = concatUpdateCondition(conditionField);

		sqlScriptBuf.append("UPDATE ");
		sqlScriptBuf.append(tableName + " ");
		sqlScriptBuf.append("SET ");
		sqlScriptBuf.append(sqlUpdateValuesStr + " ");
		sqlScriptBuf.append("WHERE ");
		sqlScriptBuf.append(sqlUpdateConditionStr);

		return sqlScriptBuf.toString();
	}
	
	/**
	 * 建立一個 Update 語法 PreparedStatement Script，欄位值的部份是採用 ':欄位名稱' 做代替。
	 * @param tableName
	 * @param fieldsVector
	 * @param conditionField 單一欄位條件
	 * @return 
	 */
	public static String createNamedParamUpdateScript(String tableName,
											Vector<String> fieldsVector,
											String conditionField) {
		if (null == tableName || null == fieldsVector || null == conditionField) {
			return null;
		}
		
		StringBuffer sqlScriptBuf = new StringBuffer();
		
		String sqlUpdateValuesStr = concatNamedParamUpdateValues(fieldsVector);
		String sqlUpdateConditionStr = concatNamedParamUpdateCondition(conditionField);
		
		sqlScriptBuf.append("UPDATE ");
		sqlScriptBuf.append(tableName + " ");
		sqlScriptBuf.append("SET ");
		sqlScriptBuf.append(sqlUpdateValuesStr + " ");
		sqlScriptBuf.append("WHERE ");
		sqlScriptBuf.append(sqlUpdateConditionStr);
		
		return sqlScriptBuf.toString();
	}
	
	// (送多方新增)
	public static String createNamedParamUpdateScriptWithTwoConditions(String tableName,
			Vector<String> fieldsVector, String conditionField1, String conditionField2) {
			if (null == tableName || null == fieldsVector || null == conditionField1 || null == conditionField2) {
				return null;
			}
			
			StringBuffer sqlScriptBuf = new StringBuffer();
			
			String sqlUpdateValuesStr = concatNamedParamUpdateValues(fieldsVector);
			
			String sqlUpdateConditionStr1 = concatNamedParamUpdateCondition(conditionField1, ":__ConditionValue1");
			String sqlUpdateConditionStr2 = concatNamedParamUpdateCondition(conditionField2, ":__ConditionValue2");
			
			sqlScriptBuf.append("UPDATE ");
			sqlScriptBuf.append(tableName + " ");
			sqlScriptBuf.append("SET ");
			sqlScriptBuf.append(sqlUpdateValuesStr + " ");
			sqlScriptBuf.append("WHERE ");
			sqlScriptBuf.append(sqlUpdateConditionStr1);
			sqlScriptBuf.append(" AND ");
			sqlScriptBuf.append(sqlUpdateConditionStr2);
			
			return sqlScriptBuf.toString();
	}
	
	private static String concatUpdateValues(Vector<String> fieldsVector) {
		StringBuffer valuesStringBuf = new StringBuffer();
		
		if (!fieldsVector.isEmpty()) {
			for(String fieldName : fieldsVector) {
				valuesStringBuf.append(" ,");
				valuesStringBuf.append(fieldName);
				valuesStringBuf.append("=");
				valuesStringBuf.append("?");
			}
			
			valuesStringBuf.delete(0, 2);
		}
		
		return valuesStringBuf.toString();
	}
	
	private static String concatNamedParamUpdateValues(Vector<String> fieldsVector) {
		StringBuffer valuesStringBuf = new StringBuffer();
		
		if (!fieldsVector.isEmpty()) {
			for(String fieldName : fieldsVector) {
				valuesStringBuf.append(" ,");
				valuesStringBuf.append(fieldName);
				valuesStringBuf.append("=");
				valuesStringBuf.append(":" + fieldName);
			}
			
			valuesStringBuf.delete(0, 2);
		}
		
		return valuesStringBuf.toString();
	}
	
	private static String concatUpdateCondition(String conditionField) {
		StringBuffer conditionStringBuf = new StringBuffer();
		
		if (null != conditionField) {
			conditionStringBuf.append(conditionField);
			conditionStringBuf.append("=");
			conditionStringBuf.append("?");
		}
		
		return conditionStringBuf.toString();
	}
	
	// (送多方修改)
	private static String concatNamedParamUpdateCondition(String conditionField) {
		return concatNamedParamUpdateCondition(conditionField, ":__ConditionValue");
	}
	
	// (送多方新增)
	private static String concatNamedParamUpdateCondition(String conditionField, String conditionFieldValue) {
		StringBuffer conditionStringBuf = new StringBuffer();

		if (null != conditionField && null != conditionFieldValue) {
			conditionStringBuf.append(conditionField);
			conditionStringBuf.append("=");
			conditionStringBuf.append(conditionFieldValue);
		}

		return conditionStringBuf.toString();
	}
}
